﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using RBS.Core.Extension;
using RBS.Core.Model;
using RBS.DBUtility;

namespace RBS.Core.DAL
{
   public class SpDal
    {
         //当日数据

        /// <summary>
        /// 当月数据
        /// </summary>
        /// <param name="hotelid">酒店ID</param>
        /// <param name="time">查询时间</param>
        /// <returns></returns>
        public List<ThatDay> GetThatMonth(int hotelid, DateTime time)
        { 
            IDataParameter[] parameters = {
					new SqlParameter("@hotelid", SqlDbType.Int,4),
                    new SqlParameter("@time", SqlDbType.DateTime,8),
			};
            parameters[0].Value = hotelid;
            parameters[1].Value = time;
            DataSet ds = DbHelperSQL.RunProcedure("ThatMonth", parameters, "ds");
            var list = new List<ThatDay>(); 
            if (ds.Tables[0].Rows.Count > 0)
            { 
                if (ds.Tables[0] != null)
                {
                    list.AddRange(from DataRow row in ds.Tables[0].Rows select row.ConvertToEntity<ThatDay>());
                }
            }
            return list;
        }  
       public ThatDay GetThatDay(int hotelid, DateTime time)
       {
           IDataParameter[] parameters = {
					new SqlParameter("@hotelid", SqlDbType.Int,4),
                    new SqlParameter("@time", SqlDbType.DateTime,8),
			};
           parameters[0].Value = hotelid;
           parameters[1].Value = time;
           DataSet ds = DbHelperSQL.RunProcedure("ThatDay", parameters, "ds");
           var entity = new ThatDay();
           var list = new List<ThatDay>(); 
           if (ds.Tables[0].Rows.Count > 0)
           {
               if (ds.Tables[0] != null)
               {
                   list.AddRange(from DataRow row in ds.Tables[0].Rows select row.ConvertToEntity<ThatDay>());
               }
           }
           return list.FirstOrDefault();
       } 
       /// <summary>
       /// 获取剩余餐桌数
       /// </summary>
       /// <param name="hotelid"></param>
       /// <param name="time"></param>
       /// <returns></returns>
       public ApiHotelSeat GetApiHotelSeat(int hotelid, DateTime time)
       {
           IDataParameter[] parameters = {
					new SqlParameter("@hotelid", SqlDbType.Int,4),
                    new SqlParameter("@time", SqlDbType.DateTime,8)
			};
           parameters[0].Value = hotelid;
           parameters[1].Value = time;
           DataSet ds = DbHelperSQL.RunProcedure("sp_ApiHotelSeat", parameters, "ds"); 
           var list = new List<ApiHotelSeat>();
           if (ds.Tables[0].Rows.Count > 0)
           {
               if (ds.Tables[0] != null)
               {
                   list.AddRange(from DataRow row in ds.Tables[0].Rows select row.ConvertToEntity<ApiHotelSeat>());
               }
           }
           return list.FirstOrDefault();
       }
        

        #region 食客统计 
       /// <summary>
       /// 根据酒店ID统计到店食客
       /// </summary>
       /// <param name="hid">酒店ID</param>
       /// <returns></returns>
       public DataSet GetUserChart(int hid)
           {
               var strSql = new StringBuilder();
               strSql.Append("select U_Name,count(OrderState)as '订单总数',count(case when OrderState=2 then OrderState end)as '预定成功', ");
               strSql.Append("  count(case when OrderState in (4,5,6,7) then OrderState end)as '已完成',count(case when OrderState=3 then OrderState end)as '订单取消'");
               strSql.Append(" from U_User u, D_Order o where u.Id=o.U_Id  and o.H_Id=@hid group by U_Name");
               SqlParameter[] parameters = {
					    new SqlParameter("@hid", SqlDbType.Int,4)
			    };
               parameters[0].Value = hid; 
               DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
               return ds;
           } 
       #endregion



        #region 酒店统计分析报表
        
       /// <summary>
       /// 热门酒店统计
       /// </summary>
       /// <param name="beginDate"></param>
       /// <param name="endDate"></param>
       /// <returns></returns>
       public DataSet GetHotHotelChart(DateTime beginDate, DateTime endDate)
       {
           var strSql = new StringBuilder(); 
               strSql.Append(
                   "select H_Id,HotelName,COUNT(1) as orderCount from D_Order   where OrderTime between @beginDate and @endDate group by  H_Id,HotelName  order by orderCount");
               SqlParameter[] parameters = {
					    new SqlParameter("@beginDate", SqlDbType.DateTime),
                        new SqlParameter("@endDate", SqlDbType.DateTime)
			    };
               parameters[0].Value = beginDate;
               parameters[1].Value = endDate;
               DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
               return ds; 
       } 
       /// <summary>
       /// 酒店评价消费统计
       /// </summary>
       /// <param name="beginDate"></param>
       /// <param name="endDate"></param>
       /// <returns></returns>
       public DataSet GetHotelConsumptionChart()
       {
           var strSql = new StringBuilder();
           strSql.Append(
               "select o.HotelName, sum(od.Num*od.Price)/COUNT(distinct o.Id) as ac from D_Order o ,D_Order_Details od  where o.Id=od.OrderId group by o.HotelName");
           DataSet ds = DbHelperSQL.Query(strSql.ToString());
           return ds; 
       }

       /// <summary>
       /// 获取热门菜谱
       /// </summary>
       /// <param name="hid"></param>
       /// <param name="isadmin"></param>
       /// <param name="beginDate"></param>
       /// <param name="endDate"></param>
       /// <returns></returns>
       public DataSet GetHotelHotCookBook(int hid, bool isadmin, DateTime beginDate, DateTime endDate)
       {
           var strSql = new StringBuilder();
           DataSet ds;
           if (isadmin)
           {
               strSql.Append(
                   "select o.HotelName,od.CName,COUNT(od.CName) as hotCookBook from D_Order o join D_Order_Details od");
               strSql.Append(" on o.Id=od.OrderId  left join  [V_CookBook] cb on od.HCId=cb.id ");
               strSql.Append(" where cb.isDisplay=0 and  OrderTime between @beginDate and @endDate ");
               strSql.Append(" group by o.HotelName,od.CName   order by hotCookBook desc ");
               SqlParameter[] parameters =
                   {
                       new SqlParameter("@beginDate", SqlDbType.DateTime),
                       new SqlParameter("@endDate", SqlDbType.DateTime)
                   };
               parameters[0].Value = beginDate;
               parameters[1].Value = endDate;
               ds = DbHelperSQL.Query(strSql.ToString(), parameters);
               return ds;
           }
           else
           {
               ds = null;
               return ds;
           }
       } 

       #endregion
    } 
}
